﻿using System;
using System.Windows;
using System.Windows.Controls;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using ReflectionStudio.Core;
using ReflectionStudio.Core.Events;
using ReflectionStudio.Core.FTPClient;
using System.Data.OracleClient;
using System.IO;
using Intex.Tools;
namespace WPFExcelReport
{
    struct vvPACK
    {
        public string PackNr;
        public SortedList<string, DataRow> WBBSTnr;
    }
    struct vvVKPOS
    {
        public DataRow dwVKPOS;
        public int vkpPcs;
        public SortedList<string, vvPACK> listVVPACK;
    }
    class DSTVV06BCReport : BaseReport
    {
        #region ----------------PROPERTIES----------------------------------
        byte[] b;
        public IntexReptServBLClient proxy;
        public OracleConnection OracleDB;
        string WBBAUFKRS;
        string WBBAUFLFD;
        string WBBAUFPOS;
        string WBBALIEFSCH;
        string VKAUFNum = "";
        string WBBTAX;
        string DocType;
        string CellName = "";
        string CellValue = "";
        string strCompany;
        string orderComments = "备注：";
        string strExcelFile;
        public string Customer = "";
        public string Address = "";
        string DateCurrent = "";
        public string PrintType = "JustExcel";
        public List<vvVKPOS> listVKPOS = new List<vvVKPOS>();
        public FTPClient ftpClient;
        private IntexTools IntexTools;
        #endregion
        public DSTVV06BCReport()
            : base()
        {

        }
        public DSTVV06BCReport(Dictionary<string, object> dicParamater)
            : base(dicParamater)
        {
            DocType = dicParamater["DocType"].ToString().Trim();
            strCompany = dicParamater["Company"].ToString().Trim();
            strExcelFile = dicParamater["ExcelFile"].ToString().Trim();
            DateCurrent = DateTime.Now.Year.ToString() + "年" + DateTime.Now.Month.ToString() + "月" + DateTime.Now.Day.ToString() + "日";
            OracleDB = (OracleConnection)dicParamater["OracleDB"];
            proxy = (IntexReptServBLClient)dicParamater["proxy"];
            IntexTools = new IntexTools(OracleDB, proxy, "");
        } 
        private int FillInEXCEL(int colums)
        {
            int result = 1;
            try
            {
                DataTable tbWBBST = dsSource.Tables["WBBST"];
                BaseFunction m_bf = new BaseFunction(OracleDB, proxy);
                DataSet setTemp = new DataSet();
                int x = 0, startx = 10;
                int y = 2;
                int page = 0;
                int rowsperpage = 25;
                int pagestart = 10;
                int buquan = 1;
                x = startx;
                pagestart = (page) * rowsperpage + startx;

                VV06Header();
                int row = 10;
                string VKPMEEH = "";
                //int packrow = 1999;
                SortedList<string, vvVKPOS> ListVKPOS = new SortedList<string, vvVKPOS>();
                vvVKPOS vkpos = new vvVKPOS();
                vvPACK pack = new vvPACK();
                int packnr;
                string processvkpos = "";
                string processpack = "";
                string processstknr = "";
                string vkposnr = "", VKPAUFKRS, VKPAUFLFD, VKPAUFPOS;
                double STKLG = 0.0;
                double vkplg = 0;
                double vkpkg = 0, vkpgrowsskg = 0.0;
                string STKNR;
                var queryvkp = from dwVKPOS in dsSource.Tables["VKPOS"].AsEnumerable()
                               orderby dwVKPOS["VKPAUFKRS"].ToString(), dwVKPOS["VKPAUFLFD"].ToString(), dwVKPOS["VKPAUFPOS"].ToString()
                               select dwVKPOS;
                #region ==============GetVKPOSLineTotal==========
                int vkpPcs = 0;
                foreach (DataRow dwVKPOS in queryvkp)
                {
                    vkpPcs = 0;
                    vkpos = new vvVKPOS();
                    vkpos.vkpPcs = vkpPcs;
                    vkpos.dwVKPOS = dwVKPOS;
                    vkpos.listVVPACK = new SortedList<string, vvPACK>();
                    VKPAUFKRS = dwVKPOS["VKPAUFKRS"].ToString();
                    VKPAUFLFD = dwVKPOS["VKPAUFLFD"].ToString();
                    VKPAUFPOS = dwVKPOS["VKPAUFPOS"].ToString();
                    vkposnr = VKPAUFKRS + VKPAUFLFD + VKPAUFPOS;

                    if (processvkpos.Contains(vkposnr + ";"))
                        continue;

                    processvkpos += vkposnr + ";";

                    #region WBBST
                    var query = from dwWBBST in tbWBBST.AsEnumerable()
                                where dwWBBST["WBBAUFKRS"].ToString() == VKPAUFKRS
                                & dwWBBST["WBBAUFLFD"].ToString() == VKPAUFLFD
                                & dwWBBST["WBBAUFPOS"].ToString() == VKPAUFPOS
                                orderby
                                dwWBBST["WBBURLIEFSCH"].ToString(),
                                dwWBBST["WBBBEM"].ToString().Substring(0, 3),
                                dwWBBST["WBBSTKNR"].ToString()
                                select dwWBBST;
                    foreach (DataRow dwWBBST in query)
                    {
                        STKNR = dwWBBST["WBBSTKNR"].ToString();
                        if (processstknr.Contains(STKNR + ";"))
                            continue;
                        processstknr += STKNR + ";";
                        vkpPcs++;
                        packnr = 0;
                        try
                        {
                            packnr = System.Convert.ToInt32(Right("0000" + dwWBBST["WBBBEM"].ToString().Substring(0, 3), 4));
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(STKNR + "件号有误!");
                        }
                        if (packnr == 0)
                            packnr = 1;
                        string wv02packnr = dwWBBST["WBBPACKNR"].ToString();
                        if (System.Convert.ToInt32(wv02packnr) > 1)
                            packnr = System.Convert.ToInt32(wv02packnr);
                        if (!processpack.Contains(vkposnr + ";" + packnr + ";"))
                        {
                            pack = new vvPACK();
                            pack.PackNr = Right("0000" + packnr.ToString(), 4);
                            pack.WBBSTnr = new SortedList<string, DataRow>();
                            pack.WBBSTnr.Add(STKNR, dwWBBST);
                            vkpos.listVVPACK.Add(pack.PackNr, pack);
                            processpack += vkposnr + ";" + packnr + ";";
                        }
                        else
                        {
                            foreach (var vpack in vkpos.listVVPACK)
                            {
                                int packnrx = System.Convert.ToInt32(vpack.Key);
                                if (packnrx == packnr)
                                {
                                    ((vvPACK)vpack.Value).WBBSTnr.Add(STKNR, dwWBBST);
                                }
                            }
                        }
                    }
                    #endregion
                    vkpos.vkpPcs = vkpPcs;
                    ListVKPOS.Add(vkposnr, vkpos);
                }
                #endregion
                #region==============WritePosition==========
                row = 11;
                int rowx = 11; 
                int vkpbox = 0;
                int vkprolls = 0;
                int boxrolls = 0;
                int ttlbox = 0;
                
                string src = "";
                int ttlrolls = 0;
                Dictionary<string, string> makeup = new Dictionary<string, string>();
                Dictionary<string, string> packup = new Dictionary<string, string>();
                int Lines = 0;
                foreach (var vkpostmp in ListVKPOS)
                { 
                    double dellength = 0;
                    double delweight = 0.0, delgrossw = 0.0;
                    vkpos = (vvVKPOS)vkpostmp.Value;
                    if (vkpos.listVVPACK.Count == 0)
                        continue;
                    row++;
                    //rowx = row+Lines;//deleted by david
                    
                    buquan = 1;
                    vkpPcs = vkpos.vkpPcs;
                    Lines = System.Convert.ToInt16(System.Math.Ceiling((double)vkpPcs * 0.25));                    
                    rowx = row + 0;//add by david
                    FillinHeader(rowx, false);
                    DataRow dwvkp = vkpos.dwVKPOS;
                    string KRS = dwvkp["VKPAUFKRS"].ToString();
                    string LFD = System.Convert.ToInt64(dwvkp["VKPAUFLFD"].ToString()).ToString();
                    string POS = System.Convert.ToInt64(dwvkp["VKPAUFPOS"].ToString()).ToString();
                    VKPMEEH = dwvkp["VKPMEEH"].ToString();
                    #region=====VKPOS========
                    exAgent.SetCellValue(rowx + 1, 1, KRS + "-" + LFD + "-" + POS);
                    exAgent.Merge(rowx + 1, 1, rowx + 1, 5);
                    exAgent.SetCellValue(rowx + 1, 7, dwvkp["VKPART"].ToString());
                    exAgent.Merge(rowx + 1, 7, rowx + 1, 9);
                    exAgent.SetCellValue(rowx + 1, 13, "'" + dwvkp["VKPFARBE"].ToString());
                    double vkpqty = 0.01 * System.Convert.ToDouble(dwvkp["VKPMENGE1"].ToString());
                    exAgent.SetCellValue(rowx + 1, 17, vkpqty);
                    string vkpdart = GetGB2312Value(dwvkp["VKPKDARTNR"].ToString() + "/" + dwvkp["VKPKDFARBNR"].ToString());
                    exAgent.SetCellValue(rowx + 1 + 1, 7, vkpdart);
                    exAgent.Merge(rowx + 1 + 1, 7, rowx + 1 + 1, 9);
                    string VKPKDKOMM = GetGB2312Value(dwvkp["VKPKDKOMM"].ToString());
                    //"贵司合同号:" +
                    exAgent.SetCellValue(8, 24, VKPKDKOMM);
                    #endregion
 
                    vkprolls = 0;
                    vkpbox = 0;
                    row = rowx + 4;//开始箱子列表
                    string WBBAUFMSL = "";
                    string WBBPACKTYP = "";

                    string packnum = "";
                    int linepcs = 0;
                    int col = 0;

                    string UM = "Y";
                    if (VKPMEEH.Contains("M"))
                        UM = "M";
                    foreach (var packtmp in vkpos.listVVPACK)
                    {
                        packnum = packtmp.Key;
                        vkpbox++;
                        boxrolls = 0;
                        
                        double packleng = 0.0;
                        double packweight = 0.0, packgrossw = 0.0;
                        double stkweight = 0.0, stkgrossw = 0.0;
                        double innerweight = 0.0;
                        double outerweight = 0.0;
                        string pclocation = "";
                        string lotc = "";
                        //src = "00" + (src + "                                     ").Substring(0, 21) + "V";
                        //sgb = m_bf.GetDataFromSDDAT("0114", src);
                        string AUFMSL = "", PACKTYP = "";
                        int pcnum = 0;
                        foreach (var pcs in ((vvPACK)packtmp.Value).WBBSTnr)
                        {
                            boxrolls++;
                            if (linepcs == Lines)
                            {
                                pcnum = 0;
                                linepcs = 0;
                                row = rowx+4;
                                col++;
                            }
                            #region==========Makeup=============
                            DataRow dwPc = (DataRow)pcs.Value;
                            #region========YDType=============
                            string ftype, art, var, color, finish;
                            string labeltype;
                            double YLength = 0;
                            string YDType = "YD0";
                            ftype = dwPc["WBBWAKZ"].ToString();
                            art = dwPc["WBBART"].ToString();
                            var = dwPc["WBBVAR"].ToString();
                            color = dwPc["WBBFARBE"].ToString();
                            finish = dwPc["WBBBESCHICH"].ToString();
                            labeltype = IntexTools.GetABAUFM(ftype, art, var, color, finish);
                            switch (labeltype)
                            {
                                case "41":
                                case "51":
                                    YDType = "YD1";
                                    break;
                                default:
                                    YDType = "YD0";
                                    break;
                            }
                            string SSTKLG = dwPc["WBBISTKLG"].ToString();
                            if (SSTKLG.Trim() == "")
                                SSTKLG = "0";
                            STKLG = System.Math.Round(System.Convert.ToDouble(SSTKLG) * 0.001, 2);
                            YLength = System.Math.Round(STKLG / 0.9144, 0);
                            if (YDType == "YD1")
                            {
                                STKLG = System.Math.Round(STKLG, 1);
                                YLength = System.Math.Round(YLength, 1);
                            }
                            else
                            {
                                STKLG = System.Math.Round(STKLG, 0);
                            }
                            if (UM == "Y")
                                STKLG = YLength;
                            STKNR = dwPc["WBBSTKNR"].ToString();
                            //int first1 = STKNR.IndexOfAny(new char[] { '1', '2', '3', '4', '5', '6', '7', '8', '9' });
                            int first1 = STKNR.IndexOf("-");
                            if (first1 > 0)
                            {
                                STKNR = STKNR.Substring(0, first1);
                            }
                            first1 = STKNR.IndexOfAny(new char[] { '1', '2', '3', '4', '5', '6', '7', '8', '9' });
                            if (first1 > 0)
                            {
                                STKNR = STKNR.Substring(first1);
                            }
                            else
                            {
                            }
                            #endregion
                            AUFMSL = dwPc["WBBAUFMSL"].ToString();
                            if (AUFMSL.Trim().Length > 0)
                            {
                                AUFMSL = "00" + (AUFMSL + "                                     ").Substring(0, 21) + "V";
                                if (makeup.ContainsKey(AUFMSL))
                                {
                                    WBBAUFMSL = makeup[AUFMSL];
                                }
                                else
                                {
                                    WBBAUFMSL = m_bf.GetSDDAT("0114", AUFMSL).Substring(61, 8);
                                    makeup.Add(AUFMSL, WBBAUFMSL);
                                }
                            }
                            if (WBBAUFMSL.Trim() == "")
                                innerweight = 0;
                            else
                            {
                                innerweight = System.Convert.ToDouble(WBBAUFMSL) * 0.01;
                            }
                            PACKTYP = dwPc["WBBPACKTYP"].ToString();
                            if (PACKTYP.Trim().Length > 0)
                            {
                                PACKTYP = "00" + (PACKTYP + "                                     ").Substring(0, 21) + "V";
                                if (packup.ContainsKey(PACKTYP))
                                {
                                    WBBPACKTYP = packup[PACKTYP];
                                }
                                else
                                {
                                    WBBPACKTYP = m_bf.GetSDDAT("0258", PACKTYP).Substring(61, 8);
                                    packup.Add(PACKTYP, WBBPACKTYP);
                                }
                            }
                            if (WBBPACKTYP.Trim() == "")
                                outerweight = 0;
                            else
                            {
                                outerweight = System.Convert.ToDouble(WBBPACKTYP) * 0.01;
                            }

                            pclocation = dwPc["WBBLO"].ToString() + "-" + dwPc["WBBLB"].ToString() + "-" + dwPc["WBBLP"].ToString();
                            stkweight = System.Math.Round(0.001 * System.Convert.ToDouble(dwPc["WBBIGEW"].ToString()), 1);
                            stkgrossw = stkweight + innerweight;
                            packleng += STKLG;
                            packweight += stkweight;
                            packgrossw += stkgrossw;
                            vkplg += STKLG;
                            #endregion
                            //row++;
                            string TAX = "";
                            TAX = dwPc["WBBTAX"].ToString();
                            if (lotc == "")
                                lotc = TAX;
                            //exAgent.AddHyperLink(row, col * 8 + 1, STKNR + "#");//add by david
                            exAgent.SetCellValue(row, col * 8 + 1, STKNR + "#");
                            //exAgent.SetCellValue(row, col * 8 + 1, buquan.ToString() + "#"); //edit by david 做了一个自然流水号
                            exAgent.SetCellValue(row, col * 8 + 5, STKLG);                            
                            //exAgent.SetCellValue(row, col * 8 + 8, lotc);
                            exAgent.HideRows(row, 1, row + 1, 1);
                            pcnum++;
                            linepcs++;
                            row++;
                            buquan++;
                        }
                        packgrossw += outerweight;
                        vkprolls += boxrolls;
                        dellength += packleng;
                        delweight += packweight;
                        delgrossw += packgrossw;
                        vkpkg += packweight;
                        vkpgrowsskg += packgrossw;
                        //exAgent.SetCellValue(row + 1, 2, lotc);
                        //exAgent.SetCellValue(row, 5, "库位:" + pclocation);
                        /*exAgent.SetCellValue(rowx + 1, 3, System.Convert.ToInt32(packnum) + "#");
                        exAgent.SetCellValue(row + 1, 5, "'" + boxrolls);
                        exAgent.SetCellValue(row + 1, 27, packleng);
                        exAgent.SetCellValue(row + 1, 30, packweight);
                        exAgent.SetCellValue(row + 1, 32, packgrossw);*/
                        exAgent.HideRows(row, 1, row, 1);
                        //row = row + 2; //deleted by david
                    }
                    ttlrolls += vkprolls;
                    ttlbox += vkpbox;
                    //exAgent.SetCellValue(rowx + 1, 19, "'" + vkpbox);
                    exAgent.SetCellValue(rowx + 1, 23, "'" + vkprolls);
                    exAgent.SetCellValue(rowx + 1, 25, dellength);
                    exAgent.SetCellValue(rowx + 1, 29, delweight);
                    exAgent.SetCellValue(rowx + 1, 31, delgrossw);

                    exAgent.ShowRows(rowx + 1, 1, rowx + 1, 1);
                    //Sub total
                    exAgent.SetCellValue(row + 3, 1, "小计:", true);
                    exAgent.Merge(row + 3, 1, row + 7, 5);
                    exAgent.SetCellValue(row + 3, 9, dellength + VKPMEEH, true);
                    exAgent.SetCellValue(row + 4, 1, "卷数:", true);
                    exAgent.Merge(row + 4, 1, row + 4, 5);
                    exAgent.SetCellValue(row +4, 9, vkprolls + "卷", true);
                }
                #endregion
                #region===============Footer================
                bool isBold = true;
                exAgent.SetCellValue(row + 10, 1, "总计:", isBold);
                exAgent.Merge(row + 10, 1, row + 10, 5);
                exAgent.SetCellValue(row + 10, 9, vkplg + VKPMEEH, isBold);
                exAgent.SetCellValue(row + 11, 1, "总重.:", isBold);
                exAgent.Merge(row + 11, 1, row + 11, 5);
                exAgent.SetCellValue(row + 11, 9, ttlrolls + "总卷数", isBold);
                exAgent.ShowRows(row + 10, 1, row + 12, 91);
                FillinFooter(row);
                exAgent.ShowRows(row + 4, 1, row + 4, 1);
                result = row + 10;
                #endregion
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return result;
        }
        private void FillinFooter(int rows)
        {
            try
            {
                //string footer = "           审单:	          发货:           核对:	          司机:           收货:             ";
                /*
                exAgent.SetCellValue(rows + 4, 3, "制单：");
                exAgent.SetCellValue(rows + 4, 7, "审单：");
                exAgent.SetCellValue(rows + 4, 11, "发货：");
                exAgent.SetCellValue(rows + 4, 15, "核对：");
                exAgent.SetCellValue(rows + 4, 19, "司机：");
                exAgent.SetCellValue(rows + 4, 23, "收货：");*/
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }

        }
        private void VV06Header()
        {
            try
            {
                #region=========Variable==============================
                int row = 7;
                string kdname = "";
                //int packrow = 1999;
                DataTable tbVVLIEF = dsSource.Tables["VVLIEF"];
                DataTable tbKUNDADR = dsSource.Tables["KUNDADR"];
                DataTable tbKDSTAMM = dsSource.Tables["KDSTAMM"];
                DataTable tbVKKOPF = dsSource.Tables["VKKOPF"];
                DataTable tbVKPOS = dsSource.Tables["VKPOS"];
                DataTable tbKDANSP = dsSource.Tables["KDANSP"];
                DataRow dwVVLIEF = tbVVLIEF.Rows[0];
                #endregion

                #region=========收件人地址==============================
                string receiver = "";
                string KUNDNR = dwVVLIEF["VVKUNDNRE"].ToString();
                row = 7;
                receiver += System.Convert.ToInt64(KUNDNR).ToString().Trim();
                string phoneR = "";
                var query = from dwKUNDDADR in tbKUNDADR.AsEnumerable()
                            where dwKUNDDADR["KDAKUNDNR"].ToString() == KUNDNR
                            & dwKUNDDADR["KDAADRSL"].ToString() == dwVVLIEF["VVADRSLL"].ToString()
                            select dwKUNDDADR;
                string address = "";
                foreach (DataRow dwKUNDDADR in query)
                {
                    phoneR = "";
                    kdname = GetGB2312Value(dwKUNDDADR["KDANAME1"].ToString().Trim());
                    receiver += "    公司名:" + kdname + Environment.NewLine;
                    string KDASTRASSE1 = GetGB2312Value(dwKUNDDADR["KDASTRASSE1"].ToString()).Trim();
                    string KDASTRASSE2 = GetGB2312Value(dwKUNDDADR["KDASTRASSE2"].ToString()).Trim();
                    address = KDASTRASSE1 + KDASTRASSE2;
                    receiver += "收货地址:" + KDASTRASSE1 + Environment.NewLine;
                    receiver += "                " + KDASTRASSE2 + Environment.NewLine;
                    phoneR += dwKUNDDADR["KDATTEX"].ToString().Trim();
                    phoneR += "/" + dwKUNDDADR["KDATEL"].ToString().Trim();
                    receiver += "收货人电话:" + phoneR;
                    continue;
                }

                exAgent.SetCellValue(3, 7, kdname);
                exAgent.SetCellValue(4, 7, address);
                //exAgent.SetCellValue(row, 2, receiver);
                DataRow dwVKKOPF = tbVKKOPF.Rows[0];
                query = from dwKDANSP in tbKDANSP.AsEnumerable()
                        where dwKDANSP["KDSKUNDNR"].ToString() == KUNDNR
                        & dwKDANSP["KDSANSPNA"].ToString() == dwVKKOPF["VKKANSPRECH"].ToString()
                        select dwKDANSP;
                string KDSANSPNAME = "";
                foreach (DataRow dwKDANSP in query)
                {
                    KDSANSPNAME = "收货人:" + GetGB2312Value(dwKDANSP["KDSANSPNAME"].ToString());
                }
                row = 10;
                KDSANSPNAME += "(" + phoneR + ")";
                //                exAgent.SetCellValue(row, 2, KDSANSPNAME);
                #endregion

                #region=========发件人信息==============================
                row = 2;
                if (dsSource.Tables.Contains("HRSTAMM"))
                {
                    string name, tel;
                    string namekey;
                    string VKKSACHBE = dwVKKOPF["VKKSACHBE"].ToString();
                    string VKKNEUBEN = dwVKKOPF["VKKNEUBEN"].ToString();
                    foreach (DataRow dwHR in dsSource.Tables["HRSTAMM"].Rows)
                    {
                        namekey = dwHR["HRBENID"].ToString();
                        name = GetGB2312Value(dwHR["HRVNAME"].ToString().Trim() + dwHR["HRNNAME"].ToString().Trim());
                        tel = "(" + dwHR["HRDTELH"].ToString().Trim() + "*" + dwHR["HRDTELD"].ToString().Trim() + ")";
                        if (namekey == VKKSACHBE)
                        {

                            //exAgent.SetCellValue(row, 21, "负责人:" + name + tel);
                            exAgent.SetCellValue(7, 7, name + tel);
                        }
                        if (namekey == VKKNEUBEN)
                        {
                            //exAgent.SetCellValue(row + 2, 21, "跟单员:" + name + tel);
                            //exAgent.SetCellValue(7, 7, name + tel);
                        }
                    }
                }
                row = 1;
                string VVLIEFSCH = "'" + System.Convert.ToInt64(dwVVLIEF["VVLIEFSCH"].ToString());
                exAgent.SetCellValue(6, 7, VVLIEFSCH);
                string printdate = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString();
                exAgent.SetCellValue(4, 24, printdate);
                #endregion

                #region=========承运人==============================
                /*
                if (dsSource.Tables.Contains("VERSART"))
                {
                    foreach (DataRow dwTemp in dsSource.Tables["VERSART"].Rows)
                    {
                        string VBEZ = "承运人:" + GetGB2312Value(dwTemp["VBEZ"].ToString()).Trim();
                        row = 3;
                        exAgent.SetCellValue(row, 21, VBEZ);
                    }
                }

                if (dsSource.Tables.Contains("TXTKON"))
                {
                    row = 5;
                    string txt = "";
                    string txtShiper = "";
                    foreach (DataRow dwVVTXT in dsSource.Tables["TXTKON"].Rows)
                    {
                        txt = GetGB2312Value(dwVVTXT["TXTKTEXT"].ToString()).Trim();
                        if (txt == "")
                            continue;
                        txtShiper += txt + Environment.NewLine;
                    }
                    exAgent.SetCellValue(row, 21, txtShiper);
                }
                */
                #endregion

                #region=========Header==============================
                row = 11;

                //exAgent.SetCellValue(row, 4, kdname);
                string VKPMEEH = dsSource.Tables["VKPOS"].Rows[0]["VKPMEEH"].ToString();
                //exAgent.SetCellValue(row, 13, VKPMEEH);
                //                exAgent.SetCellValue(row, 22, "贵司合同号");
                row = 12;
                //FillinHeader(12);
                #endregion
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private void FillinHeader(int rowx, bool flagfull = true)
        {
            try
            {
                bool flgbold = true;
                if (flagfull)
                {
                    exAgent.SetCellValue(rowx, 1, "订单号", flgbold);
                    exAgent.SetCellValue(rowx, 7, "品种", flgbold);
                    exAgent.SetCellValue(rowx, 13, "色号", flgbold);

                    //exAgent.SetCellValue(rowx, 19, "Packages",flgbold);
                    exAgent.SetCellValue(rowx, 23, "卷数", flgbold);
                    exAgent.SetCellValue(rowx, 25, "长度", flgbold);
                    exAgent.SetCellValue(rowx, 29, "净重", flgbold);
                    exAgent.SetCellValue(rowx, 31, "毛重", flgbold);
                }

                exAgent.SetCellValue(rowx + 3, 1, "卷号", flgbold);
                exAgent.SetCellValue(rowx + 3, 5, "长度", flgbold);
//                exAgent.SetCellValue(rowx + 3, 7, "批号", flgbold);
                exAgent.SetCellValue(rowx + 3, 8 * 1 + 1, "卷号", flgbold);
                exAgent.SetCellValue(rowx + 3, 8 * 1 + 5, "长度", flgbold);
   //             exAgent.SetCellValue(rowx + 3, 8 * 1 + 7, "Lot No.", flgbold);

                exAgent.SetCellValue(rowx + 3, 8 * 2 + 1, "卷号", flgbold);
                exAgent.SetCellValue(rowx + 3, 8 * 2 + 5, "长度", flgbold);
      //          exAgent.SetCellValue(rowx + 3, 8 * 2 + 7, "Lot No.", flgbold);

                exAgent.SetCellValue(rowx + 3, 8 * 3 + 1, "卷号", flgbold);
                exAgent.SetCellValue(rowx + 3, 8 * 3 + 5, "长度", flgbold);
         //       exAgent.SetCellValue(rowx + 3, 8 * 3 + 7, "Lot No.", flgbold);

                exAgent.SetCellValue(rowx, 17, "订单量", flgbold);
                exAgent.SetCellValue(rowx + 2, 1, "颜色号:", flgbold);
                exAgent.Merge(rowx + 2, 1, rowx + 2, 5);
                /*exAgent.SetCellValue(rowx + 2, 15, "匹长列表");
                exAgent.SetCellValue(rowx + 3, 1, "色光");
                exAgent.SetCellValue(rowx + 3, 3, "包号");
                exAgent.SetCellValue(rowx + 3, 5, "段数");
                */
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private void HideNullRows(int pages)
        {
            try
            {
                exAgent.HideRows((pages + 1) * 25 + 1, 1, 500, 999);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }

        private void CheckDirectory(string root, string sub, string year, string month, string day)
        {
            try
            {
                string drlocal = root;
                string dr0 = "";

                #region========================LOCAL=====================
                drlocal = Path.Combine(PathHelper.ApplicationPath, @"Report");
                if (!Directory.Exists(drlocal))
                    Directory.CreateDirectory(drlocal);

                drlocal = Path.Combine(drlocal, "Sales");
                if (!Directory.Exists(drlocal))
                    Directory.CreateDirectory(drlocal);
                #endregion
                drlocal = root;
                drlocal = Path.Combine(drlocal, sub);
                if (!Directory.Exists(drlocal))
                    Directory.CreateDirectory(drlocal);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }

        private string CreateFile(string orderno)
        {
            string filename = "";
            string sheetName = "";
            string Standard = "VV06BC";
            string filePath = Path.Combine(PathHelper.ApplicationPath, @"Report\DST\DSTStandard.xls");
            try
            {
                string pwd = "";
                sheetName = orderno;
                exAgent.Open(filePath);
                exAgent.Company = strCompany;
                exAgent.strExcelFile = strExcelFile;
                string newfilename = orderno;
                exAgent.CreateNewSheet(newfilename, Standard, "发货单", pwd);
                filename = exAgent.FileName;
                exAgent.SetCurrentSheet(1);
                int maxrow = FillInEXCEL(4);
                exAgent.CopyWorkSheet(1, 2, "细码单");
                exAgent.SetCurrentSheet("细码单");
                for (int row = 1; row < maxrow; row++)
                {
                    exAgent.ShowRows(row, 1, row, 1);
                }
                exAgent.CopyWorkSheet(1, 3, "件号列表");
                exAgent.SetCurrentSheet("件号列表");
                for (int row = 1; row < maxrow; row++)
                {
                    string value = exAgent.GetStringValue(row, 3);
                    if (value.Contains("#"))
                    {
                        exAgent.ShowRows(row, 1, row, 1);
                    }
                    value = exAgent.GetStringValue(row, 2);
                    if (value.Contains("贵司品种"))
                    {
                        exAgent.HideRows(row, 1, row, 1);
                    }
                    if (value.Contains("制单"))
                    {
                        exAgent.HideRows(row, 1, row, 1);
                    }
                    value = exAgent.GetStringValue(row, 1);
                    if (value.Contains("颜色号") || value.Contains("订单号") || value.Contains("卷号"))
                    {
                        exAgent.HideRows(row, 1, row, 1);
                    }
                }
                exAgent.DeleteWorkSheet("Sheet1");
                exAgent.DeleteWorkSheet("Sheet2");
                exAgent.DeleteWorkSheet("Sheet3");
                exAgent.SetCurrentSheet(1);

                string localFile = exAgent.FileName;
                exAgent.Save();
                exAgent.Close();
                //System.Diagnostics.Process.Start("EXCEL.EXE", localFile);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return filename;
        }

        public void CheckFTPDir(string root, string sub, string KRS, string year, string month, string day, string ordernumber = "")
        {
            string createdtimefile = PathHelper.ApplicationPath + "\\ftp.txt";
            string newfile = "/创建于" + year + month + day;
            try
            {
                string drlocal = root;
                if (!ftpClient.DirectoryExists(drlocal))
                {
                    ftpClient.CreateDirectory(drlocal);
                }
                drlocal = drlocal + "/" + sub;
                if (!ftpClient.DirectoryExists(drlocal))
                {
                    ftpClient.CreateDirectory(drlocal);
                }
                drlocal = drlocal + "/" + KRS;
                if (!ftpClient.DirectoryExists(drlocal))
                {
                    ftpClient.CreateDirectory(drlocal);
                }
                drlocal = drlocal + "/" + year;
                if (!ftpClient.DirectoryExists(drlocal))
                {
                    ftpClient.CreateDirectory(drlocal);
                }
                drlocal = drlocal + "/" + month;
                if (!ftpClient.DirectoryExists(drlocal))
                {
                    ftpClient.CreateDirectory(drlocal);
                    ftpClient.PutFile(createdtimefile, drlocal + newfile);
                }
                if (ordernumber != "")
                {
                    drlocal = drlocal + "/" + ordernumber;
                    if (!ftpClient.DirectoryExists(drlocal))
                    {
                        ftpClient.CreateDirectory(drlocal);
                        ftpClient.PutFile(createdtimefile, drlocal + newfile);
                    }
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private void FTPShareFile(string KRS, string year, string month, string day, string ordernumer, string fileType)
        {
            DataRow dwVVLIEF = dsSource.Tables["VVLIEF"].Rows[0];
            string VVLIEFSCH = System.Convert.ToInt64(dwVVLIEF["VVLIEFSCH"].ToString()).ToString();
            string fileabsname = ordernumer + "_" + DateTime.Now.Day.ToString() + "_"
                    + DateTime.Now.ToShortTimeString().Replace(":", "_").Replace(" ", "_").Replace("/", "_") + "_" + VVLIEFSCH + ".xls";
            string localFile = @"C:\Windows\Temp\" + fileabsname;
            string tempfile = "";
            try
            {
                ftpClient.Open();
                ftpClient.Login();
                string root = "/Sales/";
                ftpClient.SetCurrentDirectory("/");
                CheckFTPDir("/Sales", "发货单", KRS, year, month, day);
                string destfile;
                filename = root + "发货单/" + KRS + "/" + year + "/" + month + "/" + fileabsname;
                if (ftpClient.FileExists(filename))
                {
                    ftpClient.RemoveFile(filename);
                }

                #region==========产生文件，复制到服务器=======================
                destfile = filename;
                tempfile = CreateFile(fileabsname);
                localFile = tempfile;
                if (ftpClient.FileExists(destfile))
                {
                    ftpClient.RemoveFile(destfile);
                }
                ftpClient.PutFile(tempfile, destfile);
                localFile = @"C:\Windows\Temp\" + fileabsname;
                ftpClient.GetFile(filename, localFile, false);
                #endregion
                #region=========打开服务器上的文件进行浏览========================
                if (fileType == "OpenExcel" ||
                    fileType == "EXCEL"
                    )
                {
                    localFile = @"C:\Windows\Temp\" + fileabsname;
                    if (File.Exists(localFile))
                    {
                        File.Delete(localFile);
                    }
                    File.Move(tempfile, localFile);
                    System.Diagnostics.Process.Start("EXCEL.EXE", localFile);
                }
                #endregion
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            finally
            {
                if (File.Exists(tempfile))
                {
                    File.Delete(tempfile);
                }
            }
        }
        public override void WriteExcelReport(string fileType)
        {
            bool IsFTP = true;
            try
            {
                #region==========准备程序===================
                strExcelFile = @"DST\DSTStandard.xls";
                DataRow dwVKPOS = dsSource.Tables["VKPOS"].Rows[0];
                string KRS = dsSource.Tables["VKPOS"].Rows[0]["VKPAUFKRS"].ToString().Trim();
                string LFD = dsSource.Tables["VKPOS"].Rows[0]["VKPAUFLFD"].ToString().Trim();
                long flfd = 0;
                flfd = System.Convert.ToInt64(LFD);
                string newfilename = KRS.Trim() + flfd.ToString();
                exAgent.CheckWorkBook(newfilename);
                #endregion
                if (IsFTP)
                {
                    DataRow dwVVLIEF = dsSource.Tables["VVLIEF"].Rows[0];
                    string year = dwVVLIEF["VVNEUDAT"].ToString().Substring(0, 4);
                    string month = dwVVLIEF["VVNEUDAT"].ToString().Substring(4, 2);
                    string day = dwVVLIEF["VVNEUDAT"].ToString().Substring(6, 2);
                    FTPShareFile(KRS, year, month, day, KRS + LFD, fileType);
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }

    }
}
